'************************************************************************************
' Copyright © 2003-2005 Kathleen Dollard
'
' This software is provided 'as-is', without any express or implied warranty. In no 
' event will the author be held liable for any damages arising from the use of this
' software.
' 
' You may use this software for any purpose, including commercial applications, 
' and you may redistribute this software as part of application delivery: however, 
' this copyright notice may not be removed and you may not otherwise redistribute 
' this software directly other than providing a link to wwww.gendotnet.com. 
'
' You may alter this software to better server your needs (I'd like to hear about your 
' changes in order to make the software better). If you alter the softwre, you must  
' clearly mark it as altered and must not be misrepresented as the original software.
' nor wholly your original work.
'*************************************************************************************
Option Strict On
Option Explicit On 

Imports System

Namespace KADGen.CodeGenerationSupport
   Public Class RunSqlScripts
      Inherits MultiPass

#Region "Class level declarations"
      Private mconnection As Data.SqlClient.SqlConnection
#End Region

#Region "Public properties and methods"
      Public Sub Run( _
                     ByVal node As Xml.XmlNode, _
                     ByVal runUtility As RunUtility, _
                     ByVal statusDirective As IStatusDirective)
         Dim runSqlScriptsDirective As CodeGenerationSupport.IRunSQLScriptDirective
         Dim multiPassDirective As CodeGenerationSupport.IMultiPassDirective

         runSqlScriptsDirective = CType(node, CodeGenerationSupport.IRunSQLScriptDirective)
         multiPassDirective = CType(node, CodeGenerationSupport.IMultiPassDirective)
         Try
            mconnection = New Data.SqlClient.SqlConnection( _
                  "integrated security=SSPI" & _
                  ";data source=" & runSqlScriptsDirective.Server & _
                  ";persist security info=False" & _
                  ";initial catalog=" & runSqlScriptsDirective.Database)
            'mConnection = New Data.SqlClient.SqlConnection( _
            '      "workstation id=" & runSqlScriptsDirective.Server & _
            '      ";packet size=4096;integrated security=SSPI" & _
            '      ";data source=" & runSqlScriptsDirective.Server & _
            '      ";persist security info=False" & _
            '      ";initial catalog=" & runSqlScriptsDirective.Database)
            Me.MultiPass(multiPassDirective, _
                     Nothing, Nothing, _
                     AddressOf RunSqlScriptsCallBack, _
                     statusDirective, _
                     runUtility)
         Catch ex As System.Exception
            ' Exception should already be recorded
         End Try
      End Sub
#End Region

      '#Region "Protected properties and methods"
      '#End Region

#Region "Private properties and methods"
      Private Function RunSqlScriptsCallBack( _
               ByVal outputFileName As String, _
               ByVal multiPassDirective As CodeGenerationSupport.IMultiPassDirective, _
               ByVal nodeSelect As Xml.XmlNode, _
               ByVal runUtility As CodeGenerationSupport.RunUtility, _
               ByVal statusOutput As CodeGenerationSupport.IStatusOutput) _
               As IO.Stream
         RunScript(multiPassDirective, outputFileName, runUtility, statusOutput)
         Return Nothing
      End Function


      Public Sub RunScript( _
               ByVal multiPassDirective As CodeGenerationSupport.IMultiPassDirective, _
               ByVal filename As String, _
               ByVal runUtility As CodeGenerationSupport.RunUtility, _
               ByVal statusOutput As CodeGenerationSupport.IStatusOutput)
         Const source As String = "RunSQLScripts.RunScript"
         Dim trans As Data.sqlClient.SqlTransaction
         Dim cmd As New Data.SqlClient.SqlCommand
         Dim reader As IO.StreamReader = Nothing
         Dim fileChanged As Utility.FileChanged
         Dim sProcOldCode As String
         Dim sProcNewCode As String
         Dim sProcName As String
         Dim iProcStatementPos As Int32
         Dim runSqlScriptDirective As CodeGenerationSupport.IRunSQLScriptDirective
         Dim outputRule As CodeGenerationSupport.IOutputRuleSupportElement
         runSqlScriptDirective = CType(multiPassDirective, CodeGenerationSupport.IRunSQLScriptDirective)
         outputRule = runSqlScriptDirective.OutputRule
         If IO.File.Exists(filename) Then
            Try
               reader = New IO.StreamReader(filename)
               Dim statements() As String = Text.RegularExpressions.Regex.Split( _
                        reader.ReadToEnd, "\sgo\s", _
                        Text.RegularExpressions.RegexOptions.Compiled Or _
                        Text.RegularExpressions.RegexOptions.CultureInvariant Or _
                        Text.RegularExpressions.RegexOptions.IgnoreCase)
               iProcStatementPos = GetProcStatementPosition(statements)
               sProcName = RetrieveStoredProcName(statements, iProcStatementPos)
               sProcOldCode = RetrieveStoredProcContents(sProcName, runSqlScriptDirective)
               If sProcOldCode Is Nothing Then
                  fileChanged = Utility.FileChanged.FileDoesntExist
               Else
                  fileChanged = runUtility.IsTextChanged(sProcOldCode, outputRule.OutputFileType)
               End If
               If runUtility.ShouldRun(IO.Path.GetFileName(filename), _
                        outputRule.OutputGenType, _
                        outputRule.HashWhitespace, _
                        outputRule.OutputFileType, statusOutput) Then
                  Try
                     mconnection.Open()
                     'trans = mconnection.BeginTransaction
                     cmd.Connection = mconnection
                     'cmd.Transaction = trans
                     cmd.CommandType = Data.CommandType.Text

                     For i As Int32 = 0 To statements.Length - 1
                        If i = iProcStatementPos Then
                           sProcNewCode = runUtility.ApplyHash(statements(i), _
                                    outputRule.OutputGenType, outputRule.OutputFileType)
                           cmd.CommandText = sProcNewCode
                        Else
                           cmd.CommandText = statements(i)
                        End If
                        cmd.ExecuteNonQuery()
                     Next
                     'trans.Commit()

                  Catch ex As Exception
                     Diagnostics.Debug.WriteLine(ex)
                     Throw
                  Finally
                     cmd.Connection.Close()
                  End Try
               End If
            Catch ex As System.Exception
               Throw
            Finally
               If reader IsNot Nothing Then
                  reader.Close()
               End If
            End Try
         Else
            statusOutput.AddStatusFailure(source, _
                     "Could not find script file", Nothing)
         End If

      End Sub

      Private Function GetProcStatementPosition( _
                        ByVal statements() As String) _
                        As Int32
         ' TODO: Replace this with Regex that avoids finding CreateProcedure in a comment
         '       and in other ways might need to be more sophisticated
         For i As Int32 = 0 To statements.GetUpperBound(0)
            Dim s() As String = _
                  Text.RegularExpressions.Regex.Split(statements(i), "\W+create\W+procedure\W+", _
                  Text.RegularExpressions.RegexOptions.Compiled Or _
                  Text.RegularExpressions.RegexOptions.CultureInvariant Or _
                  Text.RegularExpressions.RegexOptions.IgnoreCase)
            If s.GetLength(0) > 1 Then
               Return i
            End If
         Next
      End Function

      Private Function RetrieveStoredProcName( _
                        ByVal statements() As String, _
                        ByVal iProcPos As Int32) _
                        As String
         Dim sprocName As String
         ' TODO: Replace this with Regex that avoids finding CreateProcedure in a comment
         '       and in other ways might need to be more sophisticated

         Dim s() As String = _
               Text.RegularExpressions.Regex.Split(statements(iProcPos), "\W+create\W+procedure\W+", _
               Text.RegularExpressions.RegexOptions.Compiled Or _
               Text.RegularExpressions.RegexOptions.CultureInvariant Or _
               Text.RegularExpressions.RegexOptions.IgnoreCase)
         If s.Length = 1 Then
            s = Text.RegularExpressions.Regex.Split(statements(iProcPos), "\W+create\W+view\W+", _
                  Text.RegularExpressions.RegexOptions.Compiled Or _
                  Text.RegularExpressions.RegexOptions.CultureInvariant Or _
                  Text.RegularExpressions.RegexOptions.IgnoreCase)
         End If
         If s.GetLength(0) > 1 Then
            sprocName = s(1).Trim
            ' Separate into individual words
            Dim sWords() As String = _
                  Text.RegularExpressions.Regex.Split(sprocName, "\W+\w+\W+", _
                  Text.RegularExpressions.RegexOptions.Compiled Or _
                  Text.RegularExpressions.RegexOptions.CultureInvariant Or _
                  Text.RegularExpressions.RegexOptions.IgnoreCase)
            If sWords.GetLength(0) > 0 Then
               Return sWords(0)
            End If
         End If
         Return Nothing
      End Function

      Private Function RetrieveStoredProcContents( _
                        ByVal procName As String, _
                        ByVal runSqlScriptDirective As CodeGenerationSupport.IRunSQLScriptDirective) _
                        As String
         Dim databaseName As String = runSqlScriptDirective.Database
         Dim sqlText As String = "SELECT * FROM INFORMATION_SCHEMA.ROUTINES " & _
                 " WHERE ROUTINE_CATALOG='" & databaseName & _
                 "' AND ROUTINE_TYPE='PROCEDURE'" & _
                 " AND ROUTINE_NAME='" & procName & "'"
         Dim da As New Data.SqlClient.SqlDataAdapter(sqlText, mconnection)
         Dim dt As New Data.DataTable
         da.Fill(dt)
         If dt.Rows.Count > 0 Then
            Return dt.Rows(0)("ROUTINE_DEFINITION").ToString
         End If
         Return Nothing
      End Function
#End Region
   End Class
End Namespace